--处理图例
CREATE OR REPLACE PROCEDURE
DEAL_LEGEND AUTHID CURRENT_USER
IS

BEGIN
  --' '神秘符号
  
  /**********   PDS_PARTS_FILES_T 数据处理    **********/
  --处理配件号
  UPDATE PDS_PARTS_FILES_T
  SET
    PARTNO = REPLACE(PARTNO, ' ', '');

  UPDATE PDS_PARTS_FILES_T
  SET
    CHAITU_1 = UPPER(SUBSTR(PARTNO, REGEXP_INSTR(PARTNO, '[(（]') + 1, 1))
  WHERE REGEXP_LIKE(PARTNO, '^.+[(（][a-z A-Z][)）]$');

  UPDATE PDS_PARTS_FILES_T
  SET
    CHAITU_2 = UPPER(SUBSTR(PARTNO, REGEXP_INSTR(PARTNO, '[-]') + 1))
  WHERE REGEXP_LIKE(PARTNO, '^.+-.+$');

  UPDATE PDS_PARTS_FILES_T
  SET
    CHAITU_1 = LPAD(TO_NUMBER(SUBSTR(PARTNO, INSTR(PARTNO, '*') + 1)), 2, '0')
  WHERE REGEXP_LIKE(PARTNO, '^.+\*\d+$');

  UPDATE PDS_PARTS_FILES_T
  SET
    PARTNO = UPPER(REGEXP_SUBSTR(PARTNO, '[0-9 a-z A-Z]+'));

  --调整文件版本
  UPDATE PDS_PARTS_FILES_T
  SET 
    VER = SUBSTR(VER, 1, 3),
    CHAITU_3 = SUBSTR(VER, 5, 1)
  WHERE REGEXP_LIKE(VER, '^[0-9]{3}-\w$');

  UPDATE PDS_PARTS_FILES_T
  SET
    VER = SUBSTR(VER, 1, 3)
  WHERE LENGTH(VER) = 5;

  UPDATE PDS_PARTS_FILES_T
  SET
    VER = SUBSTR(VER, 5)
  WHERE LENGTH(VER) = 7;

  UPDATE PDS_PARTS_FILES_T 
  SET 
    VER = REGEXP_SUBSTR(VER, '^\d+$');

  FOR V_R IN (SELECT PARTNO, MAX(VER) MV FROM PDS_PARTS_FILES_T GROUP BY PARTNO) 
  LOOP
    IF(V_R.MV IS NULL) THEN
      UPDATE PDS_PARTS_FILES_T SET VER = '000' WHERE PARTNO = V_R.PARTNO;
    ELSE
      UPDATE PDS_PARTS_FILES_T SET VER = LPAD(TO_NUMBER(V_R.MV) + 1, 3, '0') WHERE PARTNO = V_R.PARTNO AND VER IS NULL;
    END IF;
  END LOOP;

  FOR V_R IN (SELECT ROWID ROW_ID, ROW_NUMBER() OVER (PARTITION BY PARTNO, VER ORDER BY PICID, CHAITU_1, CHAITU_3, CHAITU_2, FILENAME) R FROM PDS_PARTS_FILES_T)
  LOOP
    UPDATE PDS_PARTS_FILES_T
    SET
      CHAITU = LPAD(V_R.R, 2, '0')
    WHERE ROWID = V_R.ROW_ID;
  END LOOP;

  --修改图文件类型
  UPDATE PDS_PARTS_FILES_T
  SET
    FILETYPE = 'jpg'
  WHERE FILETYPE NOT IN ('wmf', 'emf', 'jpg', 'bmp', 'gif', 'png');

  --调整备注
  UPDATE PDS_PARTS_FILES_T
  SET
    SERIALNO = REPLACE(SERIALNO, 'SN', 'S/N')
  WHERE SERIALNO LIKE 'SN%';

  UPDATE PDS_PARTS_FILES_T
  SET
    REAL_NAME = PARTNO || '_' || VER || '_' || CHAITU;


  /**********   PDS_PARTS_PART_HISTORY 数据处理    **********/
  UPDATE PDS_PARTS_PART_HISTORY_T
  SET
    PARTSNO = TRIM(PARTSNO),
    VERNO = TRIM(VERNO),
    PARTNO = TRIM(PARTNO),
    SN = TRIM(SN),
    PARTNUM = TRIM(PARTNUM),
    IA = TRIM(IA);

  --调整父配件号
  UPDATE PDS_PARTS_PART_HISTORY_T
  SET
    PARTSNO = REPLACE(PARTSNO, ' ', '');

  UPDATE PDS_PARTS_PART_HISTORY_T
  SET
    CHAITU_1 = UPPER(SUBSTR(PARTSNO, REGEXP_INSTR(PARTSNO, '[(（]') + 1, 1))
  WHERE REGEXP_LIKE(PARTSNO, '^.+[(（][a-z A-Z][)）]$');

  UPDATE PDS_PARTS_PART_HISTORY_T
  SET
    CHAITU_2 = UPPER(SUBSTR(PARTSNO, REGEXP_INSTR(PARTSNO, '[-]') + 1))
  WHERE REGEXP_LIKE(PARTSNO, '^.+-.+$');

  UPDATE PDS_PARTS_PART_HISTORY_T
  SET
    CHAITU_1 = LPAD(TO_NUMBER(SUBSTR(PARTSNO, INSTR(PARTSNO, '*') + 1)), 2, '0')
  WHERE REGEXP_LIKE(PARTSNO, '^.+\*\d+$');

  UPDATE PDS_PARTS_PART_HISTORY_T
  SET
    PARTSNO = UPPER(REGEXP_SUBSTR(PARTSNO, '[0-9 a-z A-Z]+'));

  --调整子配件号
  UPDATE PDS_PARTS_PART_HISTORY_T
  SET
    PARTNO = REPLACE(PARTNO, ' ', '');

  UPDATE PDS_PARTS_PART_HISTORY_T
  SET
    PARTNO = UPPER(REGEXP_SUBSTR(PARTNO, '[0-9 a-z A-Z]+'));

  --调整版本
  UPDATE PDS_PARTS_PART_HISTORY_T
  SET 
    VERNO = SUBSTR(VERNO, 1, 3),
    CHAITU_3 = SUBSTR(VERNO, 5, 1)
  WHERE REGEXP_LIKE(VERNO, '^[0-9]{3}-\w$');

  UPDATE PDS_PARTS_PART_HISTORY_T
  SET
    VERNO = SUBSTR(VERNO, 1, 3)
  WHERE LENGTH(VERNO) = 5;

  UPDATE PDS_PARTS_PART_HISTORY_T
  SET
    VERNO = SUBSTR(VERNO, 5)
  WHERE LENGTH(VERNO) = 7;

  UPDATE PDS_PARTS_PART_HISTORY_T 
  SET 
    VERNO = REGEXP_SUBSTR(VERNO, '^\d+$');

  FOR V_R IN (SELECT PARTSNO, MAX(VERNO) MV FROM PDS_PARTS_PART_HISTORY_T GROUP BY PARTSNO) 
  LOOP
    IF(V_R.MV IS NULL) THEN
      UPDATE PDS_PARTS_PART_HISTORY_T SET VERNO = '000' WHERE PARTSNO = V_R.PARTSNO;
    ELSE
      UPDATE PDS_PARTS_PART_HISTORY_T SET VERNO = LPAD(TO_NUMBER(V_R.MV) + 1, 3, '0') WHERE PARTSNO = V_R.PARTSNO AND VERNO IS NULL;
    END IF;
  END LOOP;

  FOR V_R IN (SELECT ROWID ROW_ID, DENSE_RANK() OVER (PARTITION BY PARTSNO, VERNO ORDER BY CHAITU_1, CHAITU_3, CHAITU_2) R FROM PDS_PARTS_PART_HISTORY_T)
  LOOP
    UPDATE PDS_PARTS_PART_HISTORY_T
    SET
      CHAITU = LPAD(V_R.R, 2, '0')
    WHERE ROWID = V_R.ROW_ID;
  END LOOP;

  UPDATE PDS_PARTS_PART_HISTORY_T
  SET
    REAL_NAME = PARTSNO || '_' || VERNO || '_' || CHAITU;


  /**********   USEFUL_PARTS_FILES & ERROR_PARTS_FILES   **********/
  EXECUTE IMMEDIATE 'TRUNCATE TABLE USEFUL_PARTS_FILES';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ERROR_PARTS_FILES';

  INSERT INTO USEFUL_PARTS_FILES
  SELECT
    *
  FROM PDS_PARTS_FILES_T
  WHERE PARTNO IN (SELECT PART_NO FROM PART);

  INSERT INTO ERROR_PARTS_FILES
  SELECT
    T1.*
  FROM PDS_PARTS_FILES_T T1
  LEFT JOIN USEFUL_PARTS_FILES T2 ON T2.ID = T1.ID
  WHERE T2.ID IS NULL;


  /**********   构建L_PART_NOT_EXISTS    **********/
  EXECUTE IMMEDIATE 'TRUNCATE TABLE L_PART_NOT_EXISTS';

  INSERT INTO L_PART_NOT_EXISTS
  SELECT DISTINCT
    PARTNO
  FROM ERROR_PARTS_FILES
  WHERE PARTNO IS NOT NULL;


  /**********   USEFUL_PARTS_PART_HISTORY & ERROR_PARTS_PART_HISTORY   **********/
  EXECUTE IMMEDIATE 'TRUNCATE TABLE USEFUL_PARTS_PART_HISTORY';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ERROR_PARTS_PART_HISTORY';

  INSERT INTO USEFUL_PARTS_PART_HISTORY
  SELECT
    T1.*
  FROM PDS_PARTS_PART_HISTORY_T T1
  WHERE (PARTSNO, PARTNO) IN (SELECT PARENT_PART_NO, PART_NO FROM ACT) 
  AND REAL_NAME IN (SELECT REAL_NAME FROM USEFUL_PARTS_FILES); 

  INSERT INTO ERROR_PARTS_PART_HISTORY
  SELECT
    T1.*
  FROM PDS_PARTS_PART_HISTORY_T T1
  LEFT JOIN USEFUL_PARTS_PART_HISTORY T2 ON T2.ID = T1.ID
  WHERE T2.ID IS NULL;

  DELETE FROM ERROR_PARTS_PART_HISTORY WHERE PARTSNO IS NULL OR PARTNO IS NULL;

  --处理形如"1.38.1.14"的SN
  UPDATE USEFUL_PARTS_PART_HISTORY 
  SET
    SN = SUBSTR(SN, INSTR(SN, '.', -1) + 1)
  WHERE REGEXP_LIKE(SN, '^.*\.\d+$');

  --处理形如"*10-1"的SN
  UPDATE USEFUL_PARTS_PART_HISTORY 
  SET
    SN = REPLACE(SN, '*', '')
  WHERE REGEXP_LIKE(SN, '^.*\*.*$'); 


  /**********   构建L_ACT_NOT_EXISTS    **********/
  EXECUTE IMMEDIATE 'TRUNCATE TABLE L_ACT_NOT_EXISTS';

  INSERT INTO L_ACT_NOT_EXISTS
  SELECT
    T.*,
    PRO.NAME_ZH
  FROM
  (
    SELECT DISTINCT
      PARTSNO PARENT_PART_NO,
      PARTNO PART_NO
    FROM ERROR_PARTS_PART_HISTORY
  ) T
  LEFT JOIN ACT A ON A.PARENT_PART_NO = T.PARENT_PART_NO AND A.PART_NO = T.PART_NO
  LEFT JOIN PART P ON P.PART_NO = T.PARENT_PART_NO
  LEFT JOIN MODEL M ON M.CODE = P.MODEL_CODE
  LEFT JOIN SUBMACHINE SM ON SM.CODE = M.SUBMACHINE_CODE
  LEFT JOIN MACHINE MC ON MC.CODE = SM.MACHINE_CODE
  LEFT JOIN PRODUCT PRO ON PRO.CODE = MC.PRODUCT_CODE
  WHERE A.PART_NO IS NULL;
  

  /**********   PDSN_LEGEND_FILE   **********/
  EXECUTE IMMEDIATE 'TRUNCATE TABLE PDSN_LEGEND_FILE';
  INSERT INTO PDSN_LEGEND_FILE
  (
    PART_NO,
    FILENAME,
    NOTE_ZH,
    NOTE_EN,
    CREATED_BY
  )
  SELECT
    PARTNO,
    REAL_NAME,
    NVL(SERIALNO, TO_CHAR(EDITDATE, 'YYYYMMDD HH24:MI:SS')),
    NVL(SERIALNO, TO_CHAR(EDITDATE, 'YYYYMMDD HH24:MI:SS')),
    'PDS'
  FROM USEFUL_PARTS_FILES;


  /**********   PDSN_LEGEND_STRUCT   **********/
  EXECUTE IMMEDIATE 'TRUNCATE TABLE PDSN_LEGEND_STRUCT';

  INSERT INTO PDSN_LEGEND_STRUCT
  (
    PARENT_PART_NO,
    CALLOUT,
    PART_NO,
    QTY,
    CREATED_BY,
    LEGEND_FILENAME
  )
  SELECT
    PARTSNO,
    SN,
    PARTNO,
    PARTNUM,
    'PDS',
    REAL_NAME
  FROM USEFUL_PARTS_PART_HISTORY;


  COMMIT;

END;